﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using yzhbg.entity;
using System.Text;

namespace DAL
{
    /// <summary>
    ///  一纸化报告数据访问层
    /// </summary>
    public class YZHBGDAL
    {   
         
        public DataRow GetBLOCK_BRIEFByBkid(string bkid)
        {
            bkid = "BK" + bkid.Substring(2);
            string sql = "select top 1 block_id,now,future from BLOCK_BRIEF where block_id=@bkid order by date desc";
            Tools tool = new Tools();
            tool.AddSqlParameter("bkid", SqlDbType.VarChar, bkid);
            DataTable tb= SqlHelper.ExecuteGetTable(DBManager.qgqp, CommandType.Text, sql, tool.GetSqlParameters());
            if (tb.Rows.Count > 0)
            {
                return tb.Rows[0];
            }
            else
            {
                return null;
            }
        }

        public DataTable Get走势评估()
        {
            string sql = "select * from  vw_website_industry_trend";
            return SqlHelper.ExecuteGetTable(DBManager.qgqp, CommandType.Text, sql, null);
        }

        public DataTable Get可关注股票(string bkid)
        {
            bkid = "BK" + bkid.Substring(2);
            string sql = "select stock_id from BLOCK_COMMEND_STOCK where block_id=@bkid";
            Tools tool = new Tools();
            tool.AddSqlParameter("bkid", SqlDbType.VarChar, bkid);
            DataTable tb= SqlHelper.ExecuteGetTable(DBManager.qgqp, CommandType.Text, sql, tool.GetSqlParameters());
            return tb;
        }

        public DataTable Get产业上下游(string bkid)
        {
            bkid = "BK" + bkid.Substring(2);
            string sql = "select * from BLOCK_UPDOWN where block_id=@bkid";
            Tools tool = new Tools();
            tool.AddSqlParameter("bkid", SqlDbType.VarChar, bkid);
            DataTable tb= SqlHelper.ExecuteGetTable(DBManager.qgqp, CommandType.Text, sql, tool.GetSqlParameters());
            return tb;
        }

        public DataTable Get投资要点(string bkid)
        {
            bkid = "BK" + bkid.Substring(2);
            string sql = "select top 10 * from BLOCK_INVEST where block_id=@bkid order by date desc";
            Tools tool = new Tools();
            tool.AddSqlParameter("bkid", SqlDbType.VarChar, bkid);
            DataTable tb= SqlHelper.ExecuteGetTable(DBManager.qgqp, CommandType.Text, sql, tool.GetSqlParameters());
            return tb;
        }

        public DataTable Get行业新闻(string bkid)
        {
            bkid = "BK" + bkid.Substring(2);
            string sql = "select TOP 15 * from  vw_website_industry_news  where BLOCK_ID=@bkid ORDER BY DATE DESC";
            Tools tools = new Tools();
            tools.AddSqlParameter("bkid", SqlDbType.VarChar, bkid);
            DataTable tb= SqlHelper.ExecuteGetTable(DBManager.qgqp, CommandType.Text, sql, tools.GetSqlParameters());
            return tb;
        }

        public DataTable Get新闻(long id)
        {
            string sql = "select TOP 1 news_id,date,title,contents from  vw_website_industry_news  where news_id=@id";
            Tools tool = new Tools();
            tool.AddSqlParameter("id", SqlDbType.BigInt, id);
            DataTable tb= SqlHelper.ExecuteGetTable(DBManager.qgqp, CommandType.Text, sql, tool.GetSqlParameters());
            return tb;
        }

        public DataTable Get消息评测(string stock,int top)
        {
            //string sql = "select top "+top+" * from  msg_score where MSG_NAME <>'' AND NOT MSG_NAME IS NULL AND stock_id=@stock order by date1 desc";
            string sql = "select top " + top + " *,td.TYPE_NAME AS Msg_names from  dbo.MSG_SCORE  ms INNER JOIN dbo.TYPE_DICT td ";
            sql+="ON ms.TYPE2=td.TYPE_ID AND td.TYPE_APP=1 ";
            sql+="where  stock_id=@stock order by date1 desc";
            Tools tool = new Tools();
            tool.AddSqlParameter("stock", SqlDbType.VarChar, stock);
            DataTable tb= SqlHelper.ExecuteGetTable(DBManager.qgqp, CommandType.Text, sql, tool.GetSqlParameters());
            return tb;
        }

        public DataTable Get个股消息评测(long id)
        {
            //string sql = "select  MSG_ID,MSG_NAME,DATE1,NEWS from  msg_score where MSG_ID=@id";
            string sql = " select MSG_ID,DATE1,NEWS,td.TYPE_NAME AS Msg_names from  dbo.MSG_SCORE  ms INNER JOIN dbo.TYPE_DICT td  ";
            sql+="ON ms.TYPE2=td.TYPE_ID AND td.TYPE_APP=1 ";
            sql+= "where  MSG_ID=@id ";
            Tools tool = new Tools();
            tool.AddSqlParameter("id", SqlDbType.BigInt, id);
            DataTable tb = SqlHelper.ExecuteGetTable(DBManager.qgqp, CommandType.Text, sql, tool.GetSqlParameters());
            return tb;
        }

        public DataTable Get板块消息评测()
        {
            string sql = "SELECT news_id,block_id,date,title,contents FROM vw_web_block_news";
            DataTable tb = SqlHelper.ExecuteGetTable(DBManager.qgqp, CommandType.Text, sql, null);
            return tb;
        }

        public List<GGGS> Get个股概述(string stock)
        {
            StringBuilder sbulder = new StringBuilder();
            sbulder.Append("select s.SecuCode,s.SecuAbbr,o.OrgIntro from ORGANIZATION o,SECURITY s");
            sbulder.Append(" where s.SecuID=o.OrgID ");
            sbulder.Append(" and s.SecuCategory='001001' and SecuCode=@stock" );
            Tools tool = new Tools();
            tool.AddSqlParameter("stock", SqlDbType.VarChar, stock);
            DataTable tb = SqlHelper.ExecuteGetTable(DBManager.NewDMSConnstr, CommandType.Text, sbulder.ToString(), tool.GetSqlParameters());
            List<GGGS> lst = new List<GGGS>();
            for (int i = 0; i < tb.Rows.Count; i++)
            {
                DataRow row=tb.Rows[i];
                GGGS c = new GGGS();
                c.Stock = stock;
                c.Title = Tools.GetString(row["OrgIntro"]);
                c.Contents = "";
                lst.Add(c);
            }
            return lst;
        }

        public DataTable Get机构动态(string stock)
        {
            string sql = "select * from vw_web_stock_fund_holder where SecuCode=@stock";
            Tools tool = new Tools();
            tool.AddSqlParameter("stock", SqlDbType.VarChar, stock);
            DataTable tb = SqlHelper.ExecuteGetTable(DBManager.qgqp, CommandType.Text, sql, tool.GetSqlParameters());
            return tb;
        }

        public DataTable Get个股核心数据(string stock)
        {
            //vw_web_report_stock_finance
            //BasicEPS,			--每股收益
            //NetAssetPS,			--每股净资产
            //ROE,				--净资产收益率
            //GrossRate,			--毛利率
            //NetProfitRate			--净利润同比增长率	
            //最后的2个毛利率和净利润同比增长,因为是股票医生自己算的,所以比较久的日前前还没计算,
            //因此早点的值会是0
            string sql = "select * from vw_web_report_stock_finance where SecuCode=@stock order by EndDate desc";
            Tools tool = new Tools();
            tool.AddSqlParameter("stock", SqlDbType.VarChar, stock);
            DataTable tb = SqlHelper.ExecuteGetTable(DBManager.qgqp, CommandType.Text, sql, tool.GetSqlParameters());
            return tb;
        }

        public DataTable Get财务研究(string stock)
        {
            string sql = "select * from vw_web_stock_industry where stock_id=@stock";
            Tools tool = new Tools();
            tool.AddSqlParameter("stock", SqlDbType.VarChar, stock);
            DataTable tb = SqlHelper.ExecuteGetTable(DBManager.qgqp, CommandType.Text, sql, tool.GetSqlParameters());
            return tb;
        }

        public DataTable Get机构动态2(string stock)
        {
            string sql = "select * from vw_web_stock_basic where stock_id=@stock";
            Tools tool = new Tools();
            tool.AddSqlParameter("stock", SqlDbType.VarChar, stock);
            DataTable tb = SqlHelper.ExecuteGetTable(DBManager.qgqp, CommandType.Text, sql, tool.GetSqlParameters());
            return tb;
        }


    }
}